package com.map.util;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;
import org.springframework.util.StringUtils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

/**
 * 生成excel表格
 * 
 * @author
 * 
 */
public class ExcelExportUtils {

    private static final Integer MAX_ROW = 50000;

    /**
     * 构造器
     * 
     */
    public ExcelExportUtils() {

    }

    /**
     * 生成具有一定格式excel
     * 
     * @param sheetName
     *            sheet名称,默认为sheet1
     * @param nf
     *            数字类型的格式 如:jxl.write.NumberFormat nf = new
     *            jxl.write.NumberFormat("#.##");默认无格式
     * @param content
     *            二维数组,要生成excel的数据来源
     * @param 合并项
     *            每一项的数据格式为0,1,0,2 即：把(0,1)和(0,2)合并--->第1列的第一、二个元素合并
     * @param os
     *            excel输出流
     * @param row
     *            需要水平居中的行,默认居左。以逗号分隔的字符串
     * @param col
     *            需要水平居中的列,默认居左。以逗号分隔的字符串
     */
    public static void export(String sheetName, NumberFormat nf, String[][] content,
                              String[] mergeInfo, OutputStream os, String row, String col) {

        if (content == null || os == null) {
            return;
        }
        // 默认名称
        if (StringUtils.isEmpty(sheetName)) {
            sheetName = "sheet1";
        }
        Set<Integer> rows = getInfo(row);
        Set<Integer> cols = getInfo(col);
        WritableWorkbook workbook = null;
        try {
            //如果超过65536行数据，会超出excel的行范围，需要截断进行处理 这里默认为50000一切换
            int sheetCount = 1;
            int totalCount = content.length - 1; //去除标题头
            //计算sheet的个数
            if (totalCount > MAX_ROW) {
                sheetCount = totalCount / MAX_ROW;
                if (totalCount % MAX_ROW > 0)
                    sheetCount += 1;
            }
            //第一行为标题  需要剔除
            String[] head = content[0];

            workbook = Workbook.createWorkbook(os);
            for (int k = 0, l = 1; k < sheetCount; k++, l++) {
                int maxLength = MAX_ROW;
                if (l * MAX_ROW > totalCount)
                    maxLength = totalCount - (l - 1) * MAX_ROW;
                String[][] tmp = new String[maxLength + 1][];
                tmp[0] = head;
                int index = 1;
                if (k * MAX_ROW != 0)
                    index = k * MAX_ROW + 1;
                System.arraycopy(content, index, tmp, 1, maxLength);
                //设置sheetName
                String sheetNameTmp = sheetName + (k * MAX_ROW + 1) + "-"
                                      + (MAX_ROW * l < totalCount ? maxLength * l : totalCount);
                WritableSheet sheet = workbook.createSheet(sheetNameTmp, k);
                for (int i = 0; i < tmp.length; i++) {
                    for (int j = 0; j < tmp[i].length; j++) {
                        if (tmp[i][j] == null) {
                            tmp[i][j] = "";
                        }
                        if (isNumber(tmp[i][j]) && !rows.contains(i) && !cols.contains(j)) {// 处理数字
                            Number number = null;
                            if (nf == null) {// 数字无格式
                                number = new Number(j, i, Double.valueOf(tmp[i][j]));
                            } else {// 如果有格式,按格式生成
                                jxl.write.WritableCellFormat wcfn = new jxl.write.WritableCellFormat(
                                    nf);
                                number = new Number(j, i, Double.valueOf(tmp[i][j]), wcfn);
                            }
                            sheet.addCell(number);
                        } else {// 处理非数字
                            WritableCellFormat format = new WritableCellFormat();
                            if (rows.contains(i) || cols.contains(j)) {
                                format.setAlignment(jxl.format.Alignment.CENTRE);
                            } else {
                                format.setAlignment(jxl.format.Alignment.LEFT);
                            }
                            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
                            Label label = new Label(j, i, tmp[i][j], format);
                            sheet.addCell(label);
                        }
                    }
                 merge(sheet, mergeInfo);
                }
            }

            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (WriteException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
     * 
     * @param sheetName
     *            sheet名称,默认为sheet1
     * @param content
     *            二维数组,要生成excel的数据来源
     * @param os
     *            excel输出流
     */
    public static void exportFormatExcel(String[][] content, String sheetName, OutputStream os) {
        if (content == null || os == null) {
            return;
        }
        // 默认名称
        if (StringUtils.isEmpty(sheetName)) {
            sheetName = "sheet1";
        }
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook(os);
            //如果超过65536行数据，会超出excel的行范围，需要截断进行处理 这里默认为50000一切换
            int sheetCount = 1;
            int totalCount = content.length - 1; //去除标题头
            //计算sheet的个数
            if (totalCount > MAX_ROW) {
                sheetCount = totalCount / MAX_ROW;
                if (totalCount % MAX_ROW > 0)
                    sheetCount += 1;
            }
            //第一行为标题  需要剔除
            String[] head = content[0];
            for (int k = 0, l = 1; k < sheetCount; k++, l++) {
                int maxLength = MAX_ROW;
                if (l * MAX_ROW > totalCount)
                    maxLength = totalCount - (l - 1) * MAX_ROW;
                String[][] tmp = new String[maxLength + 1][];
                tmp[0] = head;
                int index = 1;
                if (k * MAX_ROW != 0)
                    index = k * MAX_ROW + 1;
                System.arraycopy(content, index, tmp, 1, maxLength);
                //设置sheetName
                String sheetNameTmp = sheetName + (k * MAX_ROW + 1) + "-"
                                      + (MAX_ROW * l < totalCount ? maxLength * l : totalCount);
                WritableSheet sheet = workbook.createSheet(sheetNameTmp, k);
                for (int i = 0; i < tmp.length; i++) {
                    for (int j = 0; j < tmp[i].length; j++) {
                        if (tmp[i][j] == null) {
                            tmp[i][j] = "";
                        }
                        WritableCellFormat format = new WritableCellFormat();
                        if (i == 0) {
                            WritableFont font = new WritableFont(WritableFont.ARIAL, 10,
                                WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                            format.setFont(font);
                        }
                        format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                        format.setAlignment(jxl.format.Alignment.LEFT);
                        format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
                        Label label = new Label(j, i, tmp[i][j], format);
                        sheet.addCell(label);
                    }
                }
            }
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (WriteException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 生成固定格式的excel,表格都为文本,水平居左,垂直居中
     * 
     * @param sheetName
     *            sheet名称,默认为sheet1
     * @param content
     *            Map,要生成excel的数据来源
     * @param os
     *            excel输出流
     */
    public static void exportFormatExcel(Map<String, String[][]> content,
                                         String[] salary_name_array, String sheetName,
                                         OutputStream os) {
        if (content == null || os == null || content.size() == 0) {
            return;
        }
        // 默认名称
        if (content == null || os == null) {
            sheetName = "sheet1";
        }
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook(os);
            WritableSheet sheet = workbook.createSheet(sheetName, 0);
            int index = 0;
            for (int k = 0; k < salary_name_array.length; k++) {
                String[][] value = content.get(salary_name_array[k]);
                if (value != null && value.length > 0) {
                    if (index != 0) {
                        index++;
                    }
                    WritableCellFormat format1 = new WritableCellFormat();
                    format1.setAlignment(jxl.format.Alignment.LEFT);
                    format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
                    Label label1 = new Label(0, index, salary_name_array[k], format1);
                    sheet.addCell(label1);
                    for (int i = 0; i < value.length; i++) {
                        index++;
                        for (int j = 0; j < value[i].length; j++) {
                            if (value[i][j] == null) {
                                value[i][j] = "";
                            }
                            WritableCellFormat format = new WritableCellFormat();
                            format.setAlignment(jxl.format.Alignment.LEFT);
                            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

                            Label label = new Label(j, index, value[i][j], format);
                            sheet.addCell(label);
                        }
                    }
                }
            }
            workbook.write();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (WriteException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 合并表格
     * 
     * @param sheet
     *            工作表
     * @param mergeInfo
     *            要合并的表格的信息
     * @throws RowsExceededException
     * @throws NumberFormatException
     * @throws WriteException
     */
    private static void merge(WritableSheet sheet, String[] mergeInfo)
                                                                      throws RowsExceededException,
                                                                      NumberFormatException,
                                                                      WriteException {
        if (mergeInfo == null) {
            return;
        } else if (!isMergeInfo(mergeInfo)) {
            return;
        } else {
            for (String str : mergeInfo) {
                String[] temp = str.split(",");
                sheet.mergeCells(Integer.parseInt(temp[1]), Integer.parseInt(temp[0]),
                    Integer.parseInt(temp[3]), Integer.parseInt(temp[2]));
            }
        }
    }

    /**
     * 处理要居中的行或列的数据
     * 
     * @param indexes
     *            行标或列标
     * @return 行坐标或列坐标组成的集合
     */
    private static Set<Integer> getInfo(String indexes) {
        Set<Integer> set = new HashSet<Integer>();
        if (StringUtils.isEmpty(indexes)) {
            return set;
        }
        String[] temp = indexes.split(",", 0);
        for (String str : temp) {
            if (isNumeric(str)) {
                set.add(Integer.parseInt(str));
            }
        }
        return set;
    }

    /**
     * 判断字符串是否由纯数字组成
     * 
     * @param str
     *            源字符串
     * @return true是，false否
     */
    private static boolean isNumeric(String str) {
        if (StringUtils.isEmpty(str)) {
            return false;
        }
        Pattern pattern = Pattern.compile("[0-9]*");
        return pattern.matcher(str).matches();
    }

    /**
     * 判断字符串是否是数字
     * 
     * @param str
     *            源字符串
     * @return true是，false否
     */
    private static boolean isNumber(String number) {
        // 判断参数
        if (StringUtils.isEmpty(number)) {
            return false;
        }
        // 查看是否有小数点
        int index = number.indexOf(".");
        if (index < 0) {
            return isNumeric(number);
        } else {
            // 如果有多个".",则不是数字
            if (number.indexOf(".") != number.lastIndexOf(".")) {
                return false;
            }
            String num1 = number.substring(0, index);
            String num2 = number.substring(index + 1);
            return isNumeric(num1) && isNumeric(num2);
        }
    }

    /**
     * 判断合并项内容是否合法
     * 
     * @param mergeInfo
     *            合并项 每一项的数据格式为0,1,0,2即把(0,1)和(0,2)合并
     * @return true合法,false非法
     */
    private static boolean isMergeInfo(String[] mergeInfo) {
        if (mergeInfo == null) {
            return false;
        } else {
            for (String str : mergeInfo) {
                String[] temp = str.split(",");
                if (temp == null || temp.length != 4) {
                    return false;
                } else {
                    for (String s : temp) {
                        if (!isNumeric(s)) {
                            return false;
                        }
                    }
                }
            }
        }
        return true;
    }

    public static void main(String[] args) {
        /*
         * ExcelExportUtil ee = new ExcelExportUtil(); String[][] content = new
         * String[][] { { "", "第一列", null, "第三列" }, { "第一行", "aa", "2.00", "22"
         * }, { "第二行", "bb", "3.01", "32" }, { "第三行", "cc", "4.00", "41" } };
         * try { OutputStream os = new FileOutputStream("D:/test2.xls"); //
         * ee.export(null,null, content,null, os); ee.export(null, null,
         * content, new String[] { "0,1,0,2", "1,0,3,0" }, os, "0,1", "0"); }
         * catch (Exception e) { // TODO Auto-generated catch block
         * e.printStackTrace(); }
         */
        //		ExcelExportUtils ee = new ExcelExportUtils();
        //		String[][] content = new String[][] { { "第一行", "aa", "2.00", "22" }, { "第二行", "bb", "3.01", "32" }, { "第三行", "cc", "4.00", "41" } };
        //计算数据
        long start = System.currentTimeMillis();
        int count = 10;
        String[][] content = new String[count][];
        String[] head = new String[] { "标题" };
        content[0] = head;
        for (int i = 0, j = 1; i < count - 1; i++, j++) {
            String[] a = new String[1];
            a[0] = "行" + j;
            content[j] = a;
        }
        try {
            OutputStream os = new FileOutputStream("F:/01.xls");
            // ee.export(null,null, content,null, os);
            ExcelExportUtils.exportFormatExcel(content, "test", os);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        long end = System.currentTimeMillis();
        System.out.println((end - start) / 1000);
    }
}